using System;
using System.Collections.Generic;
using System.Text;
using AF_ORM.CORE;
using System.Data;
using System.Data.SqlClient;
namespace Bussiness.Manager
{


    public class CmsNewsManager : Bussiness.ManagerBase.CmsNewsManagerBase
    {
        public static CmsNewsManager CreateInstant()
        {
            return new CmsNewsManager();
        }

        public DataTable GetNewsListByLanguage(string lang)
        {
            string sql = @"
                    select nf.*
                    from cmsNews n inner join cmsNewsInfos nf on n.id = nf.newsid and nf.lang = @lang ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@lang", lang);
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql, param);
        }

        public DataTable SearchNews(Guid CategoryId, string NewsName)
        {
            string sql = "select * from viewNews";
            if ((CategoryId != Guid.Empty) && NewsName.Equals(""))
                sql += " where CategoryId = '" + CategoryId + "'";
            else if ((CategoryId == Guid.Empty) && !NewsName.ToString().Equals(""))
                sql += " where title_vn like '%" + NewsName + "%' or title_en like '%" + NewsName + "%'";
            else if ((CategoryId != Guid.Empty) && !NewsName.ToString().Equals(""))
                sql += " where CategoryId = '" + CategoryId + "' and (title_vn like '%" + NewsName + "%' or title_en like '%" + NewsName + "%') ";

            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
        }

        public DataTable SearchBranch(Guid CategoryId, string NewsName)
        {
            string sql = "select * from viewBranch";
            if ((CategoryId != Guid.Empty) && NewsName.Equals(""))
                sql += " where CategoryId = '" + CategoryId + "'";
            else if ((CategoryId == Guid.Empty) && !NewsName.ToString().Equals(""))
                sql += " where title_vn like '%" + NewsName + "%' or title_en like '%" + NewsName + "%'";
            else if ((CategoryId != Guid.Empty) && !NewsName.ToString().Equals(""))
                sql += " where CategoryId = '" + CategoryId + "' and (title_vn like '%" + NewsName + "%' or title_en like '%" + NewsName + "%') ";

            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
        }
        /// <summary>
        /// Lay thong tin gioi thieu ve cong ty
        /// </summary>
        /// <param name="lang"></param>
        /// <returns></returns>
        public DataTable GetIntro(string lang)
        {
            string sql = @" 
                                select * 
                                from viewIntro     
                                ";
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
        }

        /// <summary>
        /// Lay thong tin cac chi nhanh
        /// </summary>
        /// <param name="lang"></param>
        /// <returns></returns>
        public DataTable GetBranchList(string lang)
        {
            string sql = @" select * 
                                from viewBranch        
                                ";
            return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
        }

            public DataTable getNewsListByCategory(Guid CategoryId)
            {
                string sql = "select top 5 * from viewNews where CategoryId = '" + CategoryId + "'";
                return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString,CommandType.Text, sql);
            }


            public DataTable getNewsListRelation(Guid CategoryId, Int32 IntId)
            {
                string sql = "select top 5 * from viewNews where CategoryId = '" + CategoryId + "'"
                          + " and IntId <> '" + IntId + "'"
                          + " and CreatedDate <= (select CreatedDate from viewNews where IntId = '" + IntId + "')"
                          + " Order By CreatedDate desc ";

                return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
            }

            public DataTable getTopNews()
            {
                string sql = "SELECT TOP 4 * FROM viewNews ORDER BY NEWID()";
                return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
            }

            /// <summary>
            /// Lay danh sach tin tuc theo IntId
            /// </summary>
            /// <param name="lang"></param>
            /// <returns></returns>

            public DataTable getNewsListByIntId(int IntId)
            {
                string sql = "select * from viewNews N " 
						   + " INNER JOIN viewNewsCategory C on N.CategoryId = C.Id "
                           + " where C.IntId = '" + IntId + "' order by createdDate desc";

                return SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString,CommandType.Text,sql);
            }

            public DataTable getTopNews(int top) {
                string strSQL = "Select top "+top+" * from viewNews ORDER BY NEWID()";
                return WB.Library.SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString,CommandType.Text,strSQL);
            }

            public DataTable getTopNews(int top, Guid NewId) {
                string u = string.Empty;
                if(top != -1)
                    u = "top " + top + " ";
                string strSQL = "Select " + u + " * from viewNews  where Id_vn Not in ('" + NewId + "') ORDER BY NEWID()";
                return WB.Library.SqlHelper.ExecuteDataTable(Config.ConnectionConfig.ConnectionString,CommandType.Text,strSQL);
            }
    }
}
                    
